Measuring the Complexity of Join Enumeration in Query Optimization

Since relational database management systems typically support only diadic join operators as primitive operations, a query optimizer must choose the “best” scquence of two-way joins to achieve the N-way join of tables requested by a query. The computational complexity of this optimization process is dominated by the number of such possible sequences that must bc evaluated by the optimizer. This paper describes and measures the performance of the Starburst join enumerator, which can parameterically adjust for each query the space of join sequences that arc evaluated by the optimizer to allow or disallow (I) composite tables (i.e., tables that are themselves the result of a join) as the inner operand of a join and (2) joins between two tables having no join predicate linking them (i.e., Cartesian products). To limit the size of their optimizer’s search space, most earlier systems excludcd both of these types of plans, which can exccutc significantly faster for some queries. Dy experimentally varying the parameters of the Starburst join enumerator, we have validated analytic formulas for the number of join sequcnccs under a variety of conditions, and have proven their dependence upon the “shape” of the query. Specifically, ‘linear” queries, in which tables arc connectcd by binary predicates in a straight lint, can hc optimized in polynomial time. llence the dynamic programming techniques of System R and R* can still be used to optimize linear queries of as many as 100 tables in a reasonable amount of time! A query optimizer in a relational DRMS translates non-procedural queries into a pr0cedura.l plan for execution, typically hy generating many alternative plans, estimating the execution cost of each, and choosing the plan having the lowest estimated cost. Increasing this set offeasilile plans that it evaluates improves the chances but dots not guarantee! that it will find a bcttct plan, while increasing the (compile-time) cost for it to optimize the query. A major challenge in the design of a query optimizer is to ensure that the set of feasible plans contains cflicient plans without making the :set too big to he gcncratcd practically.